package com.mustafaiev.tair.autobase.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mustafaiev.tair.autobase.db.DBConnector;
import com.mustafaiev.tair.autobase.order.ClientOrder;

/**
 * 
 * Class which implements {@link OrderDao} Data Access Object interface
 * 
 * @author Tair Mustafaiev
 * 
 */
public class OrderDaoImpl implements OrderDao {

    private static final java.util.Date DATE = new java.util.Date();
    
    public int createOrder(ClientOrder order) throws SQLException {

        java.sql.PreparedStatement preparedStatement = null;
        Connection connection = null;
        
        try {
            connection = DBConnector.getInstance().getConnection();
            preparedStatement = connection
                    .prepareStatement("INSERT INTO "
                            + getTableName()
                            + " (ID, DRIVER_ID, CLIENT_NAME, CLIENT_PHONE, PROPOSE, CREATE_DATE) VALUES (?, ?, ?, ?, ?, ?);");
            // set the order id (default null)
            preparedStatement.setNull(1, java.sql.Types.BIGINT);
            // set the driver id (default null)
            preparedStatement.setNull(2, java.sql.Types.BIGINT);
            // set the client name
            preparedStatement.setString(3, order.getName());
            // set the client phone number
            preparedStatement.setString(4, order.getPhone());
            // set the order conditions
            preparedStatement.setString(5, order.getOrder());
            // set order creation time (in UNIX timestamp)
            preparedStatement.setLong(6, DATE.getTime());
            // execute query
            return preparedStatement.executeUpdate();
        } finally {
            DBConnector.getInstance().close(connection);
            DBConnector.getInstance().close(preparedStatement);
        }
    }

    public List<ClientOrder> readOrder(ClientOrder orderProperties) throws SQLException {

        Connection connection = null;
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        List<ClientOrder> orderInformation = new ArrayList<ClientOrder>();
        try {
            connection = DBConnector.getInstance().getConnection();
            statement = connection.prepareStatement("SELECT AO.*, AW.NAME AS DRIVER FROM " + getTableName()
                    + " AS AO " + " LEFT JOIN " + workersTable()
                    + " AS AW ON AO.DRIVER_ID = AW.ID " 
                    + " WHERE AO.ID = ?");
            statement.setInt(1, orderProperties.getId());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                ClientOrder order = new ClientOrder();
                order.setId(resultSet.getInt("ID"));
                order.setDriverName(resultSet.getString("DRIVER_ID"));
                order.setName(resultSet.getString("CLIENT_NAME"));
                order.setPhone(resultSet.getString("CLIENT_PHONE"));
                order.setOrder(resultSet.getString("PROPOSE"));
                order.setCreateDate(resultSet.getLong("CREATE_DATE"));

                orderInformation.add(order);
            }

        } finally {
            DBConnector.getInstance().close(resultSet);
            DBConnector.getInstance().close(statement);
            DBConnector.getInstance().close(connection);
        }
        return orderInformation;
    }

    public List<ClientOrder> getAllOrders() throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;
        List<ClientOrder> orders = new ArrayList<ClientOrder>();
        ResultSet resultSet = null;
        try {
            connection = DBConnector.getInstance().getConnection();
            statement = connection.prepareStatement("SELECT AO.*, AW.NAME AS DRIVER FROM " + getTableName()
                    + " AS AO " + " LEFT JOIN " + workersTable()
                    + " AS AW ON AO.DRIVER_ID = AW.ID ");
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                ClientOrder order = new ClientOrder();
                order.setId(resultSet.getInt("ID"));
                order.setDriverName(resultSet.getString("DRIVER"));
                order.setName(resultSet.getString("CLIENT_NAME"));
                order.setPhone(resultSet.getString("CLIENT_PHONE"));
                order.setOrder(resultSet.getString("PROPOSE"));
                order.setCreateDate(resultSet.getLong("CREATE_DATE"));
                orders.add(order);
            }
            return orders;
        } finally {
            DBConnector.getInstance().close(resultSet);
            DBConnector.getInstance().close(statement);
            DBConnector.getInstance().close(connection);
        }

    }

    public int deleteOrder(ClientOrder order) throws SQLException {
        Connection connection = null;
        java.sql.PreparedStatement statement = null;

        try {
            connection = DBConnector.getInstance().getConnection();
            statement = connection.prepareStatement("DELETE  FROM " + getTableName()
                    + " WHERE ID = ?");
            statement.setInt(1, order.getId());
            statement.executeUpdate();
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            DBConnector.getInstance().close(connection);
            DBConnector.getInstance().close(statement);
        }
        return 0;
    }

    public int updateOrder(ClientOrder order) throws SQLException {

        Connection connection = null;
        java.sql.PreparedStatement statement = null;

        try {
            connection = DBConnector.getInstance().getConnection();
            statement = connection
                    .prepareStatement("UPDATE "
                            + getTableName()
                            + " AS AU SET AU.DRIVER_ID = ? WHERE AU.ID = ?");
            statement.setInt(1, order.getDriverId());
            statement.setLong(2, order.getId());

            return statement.executeUpdate();
        } finally {
            DBConnector.getInstance().close(connection);
            DBConnector.getInstance().close(statement);
        }
    }

    private static String getTableName() {
        return "AUT_ORDERS";
    }
    
    private static String workersTable() {
        return "AUT_WORKERS";
    }
}
